1 Imports System.Data.SqlClient
2 Public Class frmHosteler
3 Private Sub auto()
4 Try
5 Dim Num As Integer = 0
6 con = New SqlConnection(cs)
7 con.Open()
8 Dim sql As String = ("SELECT MAX(H_ID) FROM Hosteler")
9 cmd = New SqlCommand(sql)
10 cmd.Connection = con
11 If (IsDBNull(cmd.ExecuteScalar)) Then
12 Num = 1
13 txtID.Text = Num.ToString
14 Else
15 Num = cmd.ExecuteScalar + 1
16 txtID.Text = Num.ToString
17 End If
18 cmd.Dispose()
19 con.Close()
20 con.Dispose()
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25 Sub fillHostelName()
26 Try
27 Dim CN As New SqlConnection(cs)
28 CN.Open()
29 adp = New SqlDataAdapter()
30 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(HostelName) FROM HostelInfo", CN)
31 ds = New DataSet("ds")
32 adp.Fill(ds)
33 dtable = ds.Tables(0)
34 cmbHostelName.Items.Clear()
35 For Each drow As DataRow In dtable.Rows
36 cmbHostelName.Items.Add(drow(0).ToString())
37 Next
38
39 Catch ex As Exception
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
41 End Try
42 End Sub
43 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
44 Me.Close()
45 End Sub
46 Sub Reset()
47 dtpJoiningDate.Text = Today
48 cmbStatus.SelectedIndex = -1
49 txtStudentName.Text = ""
50 txtSection.Text = ""
51 txtAdmissionNo.Text = ""
52 txtClass.Text = ""
53 txtSchoolName.Text = ""
54 cmbHostelName.SelectedIndex = -1
55 btnSave.Enabled = True
56 btnUpdate.Enabled = False
57 btnDelete.Enabled = False
58 txtAdmissionNo.Focus()
59 auto()
60 End Sub
61 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
62 Reset()
63 End Sub
64
65 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
66 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
67 MessageBox.Show("Please retrieve admission no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
68 txtAdmissionNo.Focus()
69 Exit Sub
70 End If
71 If Len(Trim(cmbHostelName.Text)) = 0 Then
72 MessageBox.Show("Please select hostel name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
73 cmbHostelName.Focus()
74 Exit Sub
75 End If
76 If Len(Trim(cmbStatus.Text)) = 0 Then
77 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
78 cmbStatus.Focus()
79 Exit Sub
80 End If
81 Try
82 con = New SqlConnection(cs)
83 con.Open()
84 Dim ct As String = "select AdmissionNo from Hosteler where AdmissionNo=@d1"
85 cmd = New SqlCommand(ct)
86 cmd.Connection = con
87 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
88 rdr = cmd.ExecuteReader()
89 If rdr.Read Then
90 MessageBox.Show("Record already exists", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
91 Reset()
92 If Not rdr Is Nothing Then
93 rdr.Close()
94 End If
95 Exit Sub
96 End If
97 con = New SqlConnection(cs)
98 con.Open()
99 Dim cb As String = "insert into Hosteler(H_ID,AdmissionNo,HostelID, JoiningDate, Status) VALUES (" & txtID.Text & ",@d1,@d2,@d3,@d4)"
100 cmd = New SqlCommand(cb)
101 cmd.Connection = con
102 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
103 cmd.Parameters.AddWithValue("@d2", txtHostelID.Text)
104 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
105 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
106 cmd.ExecuteNonQuery()
107 LogFunc(lblUser.Text, "added new hosteler '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
108 MessageBox.Show("Successfully saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
109 btnSave.Enabled = False
110 con.Close()
111 Catch ex As Exception
112 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
113 End Try
114 End Sub
115
116 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
117 If Len(Trim(txtAdmissionNo.Text)) = 0 Then
118 MessageBox.Show("Please retrieve admission no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
119 txtAdmissionNo.Focus()
120 Exit Sub
121 End If
122 If Len(Trim(cmbHostelName.Text)) = 0 Then
123 MessageBox.Show("Please select hostel name", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
124 cmbHostelName.Focus()
125 Exit Sub
126 End If
127 If Len(Trim(cmbStatus.Text)) = 0 Then
128 MessageBox.Show("Please select status", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
129 cmbStatus.Focus()
130 Exit Sub
131 End If
132 Try
133 con = New SqlConnection(cs)
134 con.Open()
135 Dim cb As String = "Update Hosteler set AdmissionNo=@d1, HostelID=@d2, JoiningDate=@d3, Status=@d4 where H_ID=" & txtID.Text & ""
136 cmd = New SqlCommand(cb)
137 cmd.Connection = con
138 cmd.Parameters.AddWithValue("@d1", txtAdmissionNo.Text)
139 cmd.Parameters.AddWithValue("@d2", txtHostelID.Text)
140 cmd.Parameters.AddWithValue("@d3", CDate(dtpJoiningDate.Text))
141 cmd.Parameters.AddWithValue("@d4", cmbStatus.Text)
142 cmd.ExecuteNonQuery()
143 LogFunc(lblUser.Text, "updated the hosteler '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
144 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
145 btnUpdate.Enabled = False
146 con.Close()
147 Catch ex As Exception
148 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
149 End Try
150 End Sub
151
152 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
153 Try
154 If MessageBox.Show("Do you really want to delete the record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) = Windows.Forms.DialogResult.Yes Then
155 delete_records()
156 End If
157 Catch ex As Exception
158 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
159 End Try
160 End Sub
161 Private Sub delete_records()
162 Try
163 Dim RowsAffected As Integer = 0
164 con = New SqlConnection(cs)
165 con.Open()
166 Dim cl As String = "select HostelerID from Hosteler,HostelFeePayment where Hosteler.H_ID=HostelFeePayment.HostelerID and HostelerID=@d1"
167 cmd = New SqlCommand(cl)
168 cmd.Connection = con
169 cmd.Parameters.AddWithValue("@d1", txtID.Text)
170 rdr = cmd.ExecuteReader()
171 If rdr.Read Then
172 MessageBox.Show("Unable to delete..Already in use in Hostel Fee Payment", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
173 If Not rdr Is Nothing Then
174 rdr.Close()
175 End If
176 Exit Sub
177 End If
178 con = New SqlConnection(cs)
179 con.Open()
180 Dim cq As String = "delete from Hosteler where H_ID= " & txtID.Text & ""
181 cmd = New SqlCommand(cq)
182 cmd.Connection = con
183 RowsAffected = cmd.ExecuteNonQuery()
184 If RowsAffected > 0 Then
185 LogFunc(lblUser.Text, "deleted the hosteler '" & txtStudentName.Text & "' having admission no. '" & txtAdmissionNo.Text & "'")
186 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
187 Reset()
188 Else
189 MessageBox.Show("No record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
190 Reset()
191 If con.State = ConnectionState.Open Then
192
193 con.Close()
194 End If
195
196 con.Close()
197 End If
198 Catch ex As Exception
199 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
200 End Try
201 End Sub
202
203 Private Sub frmHosteler_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
204 fillHostelName()
205 End Sub
206
207 Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
208 frmStudentRecord.Reset()
209 frmStudentRecord.lblSet.Text = "Hosteler Entry"
210 frmStudentRecord.ShowDialog()
211 End Sub
212
213 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
214 frmHostelerRecord.Reset()
215 frmHostelerRecord.lblSet.Text = "Hosteler Entry"
216 frmHostelerRecord.ShowDialog()
217 End Sub
218
219 Private Sub cmbHostelName_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbHostelName.SelectedIndexChanged
220 Try
221 con = New SqlConnection(cs)
222 con.Open()
223 cmd = con.CreateCommand()
224 cmd.CommandText = "SELECT HI_ID FROM HostelInfo where HostelName=@d1"
225 cmd.Parameters.AddWithValue("@d1", cmbHostelName.Text)
226 rdr = cmd.ExecuteReader()
227 If rdr.Read() Then
228 txtHostelID.Text = rdr.GetValue(0)
229 End If
230 If (rdr IsNot Nothing) Then
231 rdr.Close()
232 End If
233 If con.State = ConnectionState.Open Then
234 con.Close()
235 End If
236 Catch ex As Exception
237 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
238 End Try
239 End Sub
240 End Class
